﻿Imports System.Data.OleDb
Public Class Details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("booksConn").ConnectionString)
        oleDbConn.Open()
        If IsPostBack() Then
            Dim rmSql As String = "DELETE FROM BookRatings WHERE Traveller = @f1 and BookFkid = @f2"
            Dim rmcmd As OleDbCommand = New OleDbCommand(rmSql, oleDbConn)
            rmcmd.CommandType = CommandType.Text
            rmcmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            rmcmd.Parameters.AddWithValue("@f2", Request.Params("ID"))

            rmcmd.ExecuteNonQuery()

            Dim ratingSql As String = "INSERT INTO BookRatings(Rating,BookFkid,Traveller) VALUES (@f1,@f2,@f3)"
            Dim ratingscmd As OleDbCommand = New OleDbCommand(ratingSql, oleDbConn)
            ratingscmd.CommandType = CommandType.Text
            ratingscmd.Parameters.AddWithValue("@f1", Rating1.CurrentRating)
            ratingscmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            ratingscmd.Parameters.AddWithValue("@f3", User.Identity.Name)
            ratingscmd.ExecuteNonQuery()


        Else

            Dim hotelViewedSql As String = "INSERT INTO Bookviews(Traveller,BookFkId) VALUES (@f1,@f2)"
            Dim cmd As OleDbCommand = New OleDbCommand(hotelViewedSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd.Parameters.AddWithValue("@f2", Request.Params("ID"))

            cmd.ExecuteNonQuery()


            'like button
            Dim doesLike As String = "Select * from BookLikes where BookFkId=@f1 and Traveller=@f2"
            Dim likecmd As OleDbCommand = New OleDbCommand(doesLike, oleDbConn)
            likecmd.CommandType = CommandType.Text
            likecmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            likecmd.Parameters.AddWithValue("@f2", User.Identity.Name)

            Dim dr As OleDbDataReader = likecmd.ExecuteReader()
            If dr.HasRows() Then
                CheckBox1.Checked = "True"
                
            Else
                CheckBox1.Checked = "False"
            End If

        End If

        'avg rating
        Dim book_avg
        Dim avg As String = "Select AVG_Rating from AvgRatingView where BookFkId=@f1"
        Dim avgcmd As OleDbCommand = New OleDbCommand(avg, oleDbConn)
        avgcmd.CommandType = CommandType.Text
        avgcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

        Dim dr1 As OleDbDataReader = avgcmd.ExecuteReader()
        If dr1.HasRows() Then
            dr1.Read()
            book_avg = dr1.GetValue(0)
            Tb_AvgRating.Text = book_avg.ToString
        Else
            Tb_AvgRating.Text = "Not Rated yet"
        End If

        'number of likes
        Dim num_likes
        Dim numLikesql As String = "Select BookFkId,Count(*) AS LikeCount from BookLikes where BookFkId=@f1 GROUP BY BookFkId"
        Dim numlikescmd As OleDbCommand = New OleDbCommand(numLikesql, oleDbConn)
        numlikescmd.CommandType = CommandType.Text
        numlikescmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

        Dim dr2 As OleDbDataReader = numlikescmd.ExecuteReader()
        If dr2.HasRows() Then
            dr2.Read()
            num_likes = dr2.GetValue(1)
            Tb_NumLikings.Text = num_likes.ToString
        Else
            Tb_NumLikings.Text = "Not Liked yet"
        End If


        'number of comments
        Dim num_comm
        Dim numcommsql As String = "Select BookFkId,Count(*) AS ComCount from BookComments where BookFkId=@f1 GROUP BY BookFkId"
        Dim numlcommcmd As OleDbCommand = New OleDbCommand(numcommsql, oleDbConn)
        numlcommcmd.CommandType = CommandType.Text
        numlcommcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

        Dim dr3 As OleDbDataReader = numlcommcmd.ExecuteReader()
        If dr3.HasRows() Then
            dr3.Read()
            num_comm = dr3.GetValue(1)
            Tb_NumComments.Text = num_comm.ToString
        Else
            Tb_NumComments.Text = "Not Commented yet"
        End If

        'number of views
        Dim num_view
        Dim numviewsql As String = "Select BookFkId,Count(*) AS ViewCount from BookViews where BookFkId=@f1 GROUP BY BookFkId"
        Dim numviewcmd As OleDbCommand = New OleDbCommand(numviewsql, oleDbConn)
        numviewcmd.CommandType = CommandType.Text
        numviewcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

        Dim dr4 As OleDbDataReader = numviewcmd.ExecuteReader()
        If dr4.HasRows() Then
            dr4.Read()
            num_view = dr4.GetValue(1)
            Tb_numViews.Text = num_view.ToString
        Else
            Tb_numViews.Text = "Not Viewed yet"
        End If

        oleDbConn.Close()

    End Sub

    Protected Sub Btn_SubmitComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_SubmitComment.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("booksConn").ConnectionString)
        Dim SqlString As String = "Insert into BookComments(BookFkId,UserFkName,Title,UserComments) Values (@f1,@f2,@f3,@f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f3", TB_CommentTitle.Text)
        cmd.Parameters.AddWithValue("@f4", TB_Comment.Text)
        oleDbConn.Open()
        cmd.ExecuteNonQuery()
        oleDbConn.Close()
        Response.Redirect("details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub Btn_tag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_tag.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("booksConn").ConnectionString)
        Dim SqlString As String = "Insert into BookTagJunction(BookFkId,BookTagFkId) Values (@f1,@f2)"
        oleDbConn.Open()

        For Each cb As ListItem In CheckBoxList1.Items
            If cb.Selected Then
                'here we add the join to the table of tags and hotels
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmd.Parameters.AddWithValue("@f2", cb.Value)
                cmd.ExecuteNonQuery()
            End If
        Next

        If TextBox1.Text = "" Then
            Return
        End If
        Dim newTags = TextBox1.Text.Split(",")
        For Each newTag In newTags
            Dim newTagSql As String = "INSERT INTO BookTag(Tag) Values (@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'find the id of new tag
            Dim getLastTagIdCmd As OleDbCommand = New OleDbCommand("Select Top 1 ID from bookTag ORDER BY ID Desc", oleDbConn)
            Dim dr = getLastTagIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            'assosiate last id with book id

            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            newcmd.CommandType = CommandType.Text
            newcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            newcmd.Parameters.AddWithValue("@f2", lastId)
            newcmd.ExecuteNonQuery()

        Next

        oleDbConn.Close()
        Response.Redirect("details.aspx?ID=" & Request.Params("ID"))
    End Sub

    
    Protected Sub CheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBox1.CheckedChanged
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("booksConn").ConnectionString)
        Dim getcmd As OleDbCommand = New OleDbCommand("Select * from BookLikes where BookFkId=@f1 and Traveller=@f2", oleDbConn)
        getcmd.CommandType = CommandType.Text
        getcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        getcmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        oleDbConn.Open()

        Dim dr = getcmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            Dim rmSql As String = "DELETE FROM BookLikes WHERE Traveller = @f1 and BookFkId = @f2"
            Dim rmcmd As OleDbCommand = New OleDbCommand(rmSql, oleDbConn)
            rmcmd.CommandType = CommandType.Text
            rmcmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            rmcmd.Parameters.AddWithValue("@f2", Request.Params("ID"))

            rmcmd.ExecuteNonQuery()

        Else
            Dim SqlString As String = "Insert into BookLikes(BookFkId,Traveller) Values (@f1,@f2)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
            cmd.ExecuteNonQuery()
        End If
        oleDbConn.Close()
        Response.Redirect("details.aspx?ID=" & Request.Params("ID"))
    End Sub

    Protected Sub TextBox3_TextChanged(ByVal sender As Object, ByVal e As EventArgs)

    End Sub
End Class